from PyQt5.QtWidgets import QApplication, QMainWindow,QWidget,QTableWidgetItem,QMessageBox,QLineEdit
from PyQt5.uic import loadUiType
import pymysql

ui, _ = loadUiType('un1.ui')
login, _ = loadUiType('un2.ui')
class DEngApp(QWidget,ui):
    def __init__(self):
        super(DEngApp,self).__init__()
        self.setupUi(self)
        self.pushButton_2.clicked.connect(self.zhuce_user)
        self.pushButton.clicked.connect(self.denglu)
    def zhuce_user(self):
        db = pymysql.connect(host="localhost", user="root", passwd="", database="dbxx",charset="utf8")  # 使用pymysql.connect来连接
        cur = db.cursor()
        sql = "insert into user(id,pwd) values(%s,%s)"
        a1 = self.lineEdit.text()
        b1 = self.lineEdit_2.text()
        cur.execute(sql, (a1, b1))
        db.commit()
        cur.close()
        db.close()
        QMessageBox.information(self, "提醒", "您已成功注册账号!", QMessageBox.Yes, QMessageBox.Yes)
        self.lineEdit.setText('')
        self.lineEdit_2.setText('')
    def denglu(self):
        db = pymysql.connect(host="localhost", user="root", passwd="", database="dbxx",charset="utf8")
        cur = db.cursor()
        sql = "select * from user where id=%s and pwd=%s"
        a = self.lineEdit.text()
        pwd = self.lineEdit_2.text()
        cur.execute(sql, (a, pwd))
        data = cur.fetchone()
        if data:
            self.app = MainApp()
            self.close()
            self.app.show()

class MainApp(QMainWindow,login):
    def __init__(self):
        super(MainApp,self).__init__()
        self.setupUi(self)
        self.show_stu()
        self.pushButton.clicked.connect(self.add_st)
        self.pushButton_2.clicked.connect(self.select)
        self.pushButton_4.clicked.connect(self.update)
        self.pushButton_3.clicked.connect(self.select1)
        self.pushButton_5.clicked.connect(self.delete)
    def add_st(self):
        db = pymysql.connect(host="localhost", user="root", passwd="", database="dbxx",charset="utf8")
        cur = db.cursor()
        sql = "insert into stu(id,name,sex,class) values(%s,%s,%s,%s)"
        a1 = self.lineEdit.text()
        b1 = self.lineEdit_2.text()
        c1 = self.lineEdit_3.text()
        d1 = self.lineEdit_4.text()
        cur.execute(sql, (a1, b1,c1,d1))
        db.commit()
        cur.close()
        db.close()
        self.show_stu()
    def show_stu(self):
        db = pymysql.connect(host="localhost", user="root", passwd="", database="dbxx",charset="utf8")
        cur = db.cursor()
        sql = "select * from stu"
        cur.execute(sql)
        data = cur.fetchall()
        if data:
            self.tableWidget.setRowCount(0)
            self.tableWidget.insertRow(0)

            for row, form in enumerate(data):
                for column, item in enumerate(form):
                    self.tableWidget.setItem(row,column,QTableWidgetItem(str(item)))
                    column += 1
                row_position = self.tableWidget.rowCount()
                self.tableWidget.insertRow(row_position)
    def select(self):
        db = pymysql.connect(host="localhost", user="root", passwd="", database="dbxx",charset="utf8")
        cur = db.cursor()
        sql = "select * from stu where id=%s"
        a = self.lineEdit_13.text()
        cur.execute(sql, (a,))
        data = cur.fetchone()
        if data:
            self.lineEdit_8.setText(str(data[0]))
            self.lineEdit_7.setText(str(data[1]))
            self.lineEdit_5.setText(str(data[2]))
            self.lineEdit_6.setText(str(data[3]))
    def update(self):
        old_st_info_no = self.lineEdit_13.text()
        a = self.lineEdit_8.text()
        b = self.lineEdit_7.text()
        c = self.lineEdit_5.text()
        d = self.lineEdit_6.text()
        db = pymysql.connect(host="localhost", user="root", passwd="", database="dbxx",charset="utf8")
        cur = db.cursor()
        sql = "update stu set id=%s,name=%s,sex=%s,class=%s where id=%s"
        cur.execute(sql, (a,b,c,d,old_st_info_no))
        db.commit()
        QMessageBox.information(self, "提醒", "已修改信息成功！", QMessageBox.Yes, QMessageBox.Yes)
        self.show_stu()
    def select1(self):
        db = pymysql.connect(host="localhost", user="root", passwd="", database="dbxx",charset="utf8")
        cur = db.cursor()
        sql = "select * from stu where id=%s"
        a = self.lineEdit_14.text()
        cur.execute(sql, (a,))
        data = cur.fetchone()
        if data:
            self.lineEdit_9.setText(str(data[0]))
            self.lineEdit_12.setText(str(data[1]))
            self.lineEdit_11.setText(str(data[2]))
            self.lineEdit_10.setText(str(data[3]))
    def delete(self):
        db = pymysql.connect(host="localhost", user="root", passwd="", database="dbxx",charset="utf8")
        cur = db.cursor()
        sql = "delete from stu where id=%s"
        a = self.lineEdit_14.text()
        cur.execute(sql, (a,))
        db.commit()
        self.show_stu()

def main():
    app = QApplication([])
    window = DEngApp()
    window.show()
    app.exec_()
if __name__ == '__main__':

    main()